package com.example.huixue.mapper;

import com.example.huixue.entity.Comment;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface CommentMapper {
    
    /**
     * 插入新评论
     */
    @Insert("INSERT INTO comments (user_id, resource_id, content, rating, created_at, updated_at) " +
            "VALUES (#{userId}, #{resourceId}, #{content}, #{rating}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Comment comment);
    
    /**
     * 根据ID查找评论
     */
    @Select("SELECT c.*, u.username, u.profile_picture, res.title as resource_title " +
            "FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "WHERE c.id = #{id}")
    Comment findById(Long id);
    
    /**
     * 更新评论
     */
    @Update("UPDATE comments SET content = #{content}, rating = #{rating}, updated_at = #{updatedAt} WHERE id = #{id}")
    int update(Comment comment);
    
    /**
     * 删除评论
     */
    @Delete("DELETE FROM comments WHERE id = #{id}")
    int deleteById(Long id);
    
    /**
     * 获取资源的所有评论
     */
    @Select("SELECT c.*, u.username, u.profile_picture, res.title as resource_title " +
            "FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "WHERE c.resource_id = #{resourceId} " +
            "ORDER BY c.created_at DESC LIMIT #{offset}, #{limit}")
    List<Comment> findByResourceWithPagination(@Param("resourceId") Long resourceId, 
                                                @Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 获取资源评论总数
     */
    @Select("SELECT COUNT(*) FROM comments WHERE resource_id = #{resourceId}")
    long countByResource(Long resourceId);
    
    /**
     * 获取用户的所有评论
     */
    @Select("SELECT c.*, u.username, u.profile_picture, res.title as resource_title " +
            "FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "WHERE c.user_id = #{userId} " +
            "ORDER BY c.created_at DESC LIMIT #{offset}, #{limit}")
    List<Comment> findByUserWithPagination(@Param("userId") Long userId, 
                                            @Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 获取用户评论总数
     */
    @Select("SELECT COUNT(*) FROM comments WHERE user_id = #{userId}")
    long countByUser(Long userId);
    
    /**
     * 获取最新评论
     */
    @Select("SELECT c.*, u.username, u.profile_picture, res.title as resource_title " +
            "FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "ORDER BY c.created_at DESC LIMIT #{limit}")
    List<Comment> findLatestComments(@Param("limit") int limit);
    
    /**
     * 获取评论总数
     */
    @Select("SELECT COUNT(*) FROM comments")
    long countAll();
    
    /**
     * 删除用户的所有评论
     */
    @Delete("DELETE FROM comments WHERE user_id = #{userId}")
    int deleteByUser(Long userId);
    
    /**
     * 删除资源的所有评论
     */
    @Delete("DELETE FROM comments WHERE resource_id = #{resourceId}")
    int deleteByResource(Long resourceId);
    
    /**
     * 管理员获取所有评论（分页）
     */
    @Select("SELECT c.*, u.username, u.profile_picture, res.title as resource_title " +
            "FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "ORDER BY c.created_at DESC LIMIT #{offset}, #{limit}")
    List<Comment> findAllWithPagination(@Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 管理员根据关键词搜索评论（分页）
     */
    @Select("SELECT c.*, u.username, u.profile_picture, res.title as resource_title " +
            "FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "WHERE c.content LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR u.username LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR res.title LIKE CONCAT('%', #{keyword}, '%') " +
            "ORDER BY c.created_at DESC LIMIT #{offset}, #{limit}")
    List<Comment> findAllWithKeywordPagination(@Param("keyword") String keyword, 
                                               @Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 管理员根据关键词搜索评论总数
     */
    @Select("SELECT COUNT(*) FROM comments c " +
            "LEFT JOIN users u ON c.user_id = u.user_id " +
            "LEFT JOIN resources res ON c.resource_id = res.resource_id " +
            "WHERE c.content LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR u.username LIKE CONCAT('%', #{keyword}, '%') " +
            "   OR res.title LIKE CONCAT('%', #{keyword}, '%')")
    long countAllWithKeyword(@Param("keyword") String keyword);
} 